<?php

namespace app\api\controller;

use app\common\traits\ApiTrait;
use Juhelib\excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use support\Request;
use support\Response;
use think\facade\Db;
use app\extend\WxUtil\WxTool;
use Tinywan\Jwt\JwtToken;
use function Symfony\Component\String\b;

class StaffController
{
    use ApiTrait;

    protected $noNeedLogin = ['exportOrder'];

    /**
     * 绑定活动
     * @param Request $request
     * @return Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function binding(Request $request) : Response
    {
        if (!request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->post('activityId');

        $activity = Db::name('activity')->where(['id' => $activityId])->find();
        if (empty($activity['user_id'])) {
            Db::name('activity')->where(['id' => $activityId])->update(['user_id' => JwtToken::getExtend()['id']]);

            // 判断是否有活动角色数据
            $user_role = Db::name('activity_user_role')->where(['user_id' => JwtToken::getExtend()['id'], 'activity_id' => $activityId])->find();
            if (!empty($user_role)) {
                Db::name('activity_user_role')->where(['user_id' => JwtToken::getExtend()['id'], 'activity_id' => $activityId])->update(['role' => 1, 'create_time' => date('Y-m-d H:i:s')]);
            } else {
                Db::name('activity_user_role')->insert(['user_id' => JwtToken::getExtend()['id'], 'activity_id' => $activityId, 'role' => 1, 'create_time' => date('Y-m-d H:i:s')]);
            }
            return $this->reSuccess('绑定成功');
        } else {
            return $this->reError('绑定失败', false);
        }
    }

    /**
     * 核销
     * @return void
     */
    public function confirmPrize(Request $request) : Response
    {
        if (!request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $prizeId = $request->post('prizeId');

        switch ($request->post('type')) {
            // 翻牌抽奖奖品
            case 1:
                // 判断是否有核销资格
                $qualification = Db::name('payment_activity_prize')
                    ->alias('a')
                    ->join('activity b', 'a.activity_id = b.id')
                    ->where(['b.user_id' => JwtToken::getExtend()['id'], 'a.id' => $prizeId])
                    ->find();
                if (!empty($qualification)) {
                    // 必须领取后才能核销
                    $result = Db::name('payment_activity_prize')
                        ->where(['id' => $prizeId, 'receive_status' => 1])
                        ->update(['operator_user_id' => JwtToken::getExtend()['id'], 'status' => 2, 'confirm_time' => date('Y-m-d H:i:s')]);
                }
                break;
            // 平台盲盒奖品
            case 2:
                // 判断是否有核销资格
                $qualification = Db::name('payment_platform_box')
                    ->alias('a')
                    ->join('activity b', 'a.activity_id = b.id')
                    ->where(['b.user_id' => JwtToken::getExtend()['id'], 'a.id' => $prizeId])
                    ->find();
                if (!empty($qualification)) {
                    // 核销
                    $result = Db::name('payment_platform_box')
                        ->where(['id' => $prizeId])
                        ->update(['operator_user_id' => JwtToken::getExtend()['id'], 'status' => 2, 'confirm_time' => date('Y-m-d H:i:s')]);
                }
                break;
        }
        return (!empty($result)) ? $this->reSuccess('核销成功') : $this->reError('核销失败', false);
    }

    /**
     * 活动数据
     * @param Request $request
     * @return Response
     */
    public function dashboard(Request $request) : Response
    {
        $activityId = $request->get('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }
        
        // 获取时间筛选条件
        $startDate = $request->get('startDate');
        $endDate = $request->get('endDate');
        
        // 构建时间筛选条件
        $timeWhere = [];
        if (!empty($startDate) && !empty($endDate)) {
            $timeWhere = [
                ['create_time', '>=', strtotime($startDate)],
                ['create_time', '<=', strtotime($endDate . ' 23:59:59')]
            ];
        }
        
        // 构建支付时间筛选条件
        $payTimeWhere = [];
        if (!empty($startDate) && !empty($endDate)) {
            $payTimeWhere = [
                ['pay_time', '>=', strtotime($startDate)],
                ['pay_time', '<=', strtotime($endDate . ' 23:59:59')]
            ];
        }
        
        // 构建发放时间筛选条件
        $disburseTimeWhere = [];
        if (!empty($startDate) && !empty($endDate)) {
            $disburseTimeWhere = [
                ['disburse_time', '>=', strtotime($startDate)],
                ['disburse_time', '<=', strtotime($endDate . ' 23:59:59')]
            ];
        }

        // 今日收款
        $data['today_receive'] = Db::name('order')->whereDay('pay_time')->where([['activity_id', '=', $activityId], ['order_status', '=', 2]])->sum('total_price');
        // 今日支出
        $data['today_disburse'] = Db::name('payment_disburse')->whereDay('disburse_time')->where([['activity_id', '=', $activityId], ['status', '=', 2]])->sum('amount');
        // 今日订单数
        $data['today_order_num'] = Db::name('order')->whereDay('create_time')->where([['activity_id', '=', $activityId]])->count();
        // 今日礼品中奖
        $data['today_activity_prize_num'] = Db::name('payment_activity_prize')->whereDay('create_time')->where([['activity_id', '=', $activityId]])->count();
        // 今日盲盒中奖
        $data['today_box_prize_num'] = Db::name('payment_platform_box')->whereDay('create_time')->where([['activity_id', '=', $activityId]])->count();
        
        // 总收款
        $query = Db::name('order')->where([['activity_id', '=', $activityId], ['order_status', '=', 2]]);
        if (!empty($payTimeWhere)) {
            $query->where($payTimeWhere);
        }
        $data['total_receive'] = $query->sum('total_price');
        
        // 总支出
        $query = Db::name('payment_disburse')->where([['activity_id', '=', $activityId], ['status', '=', 2]]);
        if (!empty($disburseTimeWhere)) {
            $query->where($disburseTimeWhere);
        }
        $data['total_disburse'] = $query->sum('amount');
        
        // 总订单数
        $query = Db::name('order')->where([['activity_id', '=', $activityId]]);
        if (!empty($timeWhere)) {
            $query->where($timeWhere);
        }
        $data['total_order_num'] = $query->count();
        
        // 总礼品中奖
        $query = Db::name('payment_activity_prize')->where([['activity_id', '=', $activityId]]);
        if (!empty($timeWhere)) {
            $query->where($timeWhere);
        }
        $data['total_activity_prize_num'] = $query->count();
        
        // 总盲盒中奖
        $query = Db::name('payment_platform_box')->where([['activity_id', '=', $activityId]]);
        if (!empty($timeWhere)) {
            $query->where($timeWhere);
        }
        $data['total_box_prize_num'] = $query->count();

        return $this->reSuccess('数据获取成功', $data ?? []);
    }

    /**
     * 活动订单
     * @param Request $request
     * @return Response
     */
    public function dataOrder(Request $request) : Response
    {
        if (!request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->post('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $type = $request->post('type');
        $page = $request->post('page');
        $limit = $request->post('limit');
        
        // 获取筛选条件
        $startDate = $request->post('startDate');
        $endDate = $request->post('endDate');
        $nickname = $request->post('nickname');
        $realname = $request->post('realname');
        $mobile = $request->post('mobile');
        $payStatus = $request->post('payStatus');
        
        // 构建时间筛选条件
        $timeWhere = [];
        if (!empty($startDate) && !empty($endDate)) {
            $timeWhere = [
                ['a.create_time', '>=', date('Y-m-d H:i:s', strtotime($startDate . ' 00:00:00'))],
                ['a.create_time', '<=', date('Y-m-d H:i:s', strtotime($endDate . ' 23:59:59'))]
            ];
        }

        switch ($type) {
            case 1:
                // 活动订单
                $query = Db::name('order')
                    ->alias('a')
                    ->join('user b', 'a.user_id = b.id')
                    ->join('user c', 'a.recom_user_id = c.id')
                    ->join('activity d', 'a.activity_id = d.id')
                    ->join('user e', 'a.clerk_user_id = e.id')
                    ->where([['a.activity_id', '=', $activityId]]);
                
                // 支付状态筛选
                if ($payStatus == 1) {
                    $query->where('a.order_status', '<>', 2);
                } elseif ($payStatus == 2) {
                    $query->where('a.order_status', 2);
                }
                
                // 用户信息筛选
                if (!empty($nickname)) {
                    $query->where('b.nickname', 'like', '%' . $nickname . '%');
                }
                if (!empty($realname)) {
                    $query->where('a.name', 'like', '%' . $realname . '%');
                }
                if (!empty($mobile)) {
                    $query->where('a.phone', 'like', '%' . $mobile . '%');
                }

                // 时间筛选
                if (!empty($timeWhere)) {
                    $query->where($timeWhere);
                }
                
                $data = $query->field('a.*, d.activity_title, b.nickname, b.avatar_url, c.nickname as recom_nickname, e.nickname as clerk_nickname')
                    ->order('a.id desc')
                    ->page($page, $limit)->select()->toArray();
                break;
            case 2:
                // 推荐奖励
                $query = Db::name('payment_disburse')
                    ->alias('a')
                    ->join('user u', 'a.user_id = u.id')
                    ->join('order o', 'a.order_id = o.id')
                    ->where([['a.activity_id', '=', $activityId]])
                    ->field('a.*, u.nickname, u.avatar_url, o.name, o.phone, o.total_price');

                // 用户信息筛选
                if (!empty($nickname)) {
                    $query->where('u.nickname', 'like', '%' . $nickname . '%');
                }
                if (!empty($realname)) {
                    $query->where('o.name', 'like', '%' . $realname . '%');
                }
                if (!empty($mobile)) {
                    $query->where('o.phone', 'like', '%' . $mobile . '%');
                }
                
                // 时间筛选
                if (!empty($timeWhere)) {
                    $query->where($timeWhere);
                }
                
                $data = $query->order('a.id desc')
                    ->page($page, $limit)
                    ->select()
                    ->toArray();

                // 获取来源订单信息
                foreach ($data as &$value) {
                    $value['source'] = Db::name('order')
                        ->alias('o')
                        ->join('user u', 'o.user_id = u.id', 'left')
                        ->where(['o.id' => $value['order_id']])
                        ->field('u.nickname, u.avatar_url, o.name, o.phone')
                        ->find();
                }
                break;
            case 3:
                // 礼品订单
                $query = Db::name('payment_activity_prize')
                    ->alias('a')
                    ->join('activity_prize b', 'a.activity_prize_id = b.id')
                    ->join('user c', 'a.user_id = c.id')
                    ->join('order d', 'a.order_id = d.id', 'left')
                    ->where([['a.activity_id', '=', $activityId]]);

                // 用户信息筛选
                if (!empty($nickname)) {
                    $query->where('c.nickname', 'like', '%' . $nickname . '%');
                }
                if (!empty($realname)) {
                    $query->where('d.name', 'like', '%' . $realname . '%');
                }
                if (!empty($mobile)) {
                    $query->where('d.phone', 'like', '%' . $mobile . '%');
                }
                
                // 时间筛选
                if (!empty($timeWhere)) {
                    $query->where($timeWhere);
                }
                
                $data = $query->field('a.*, c.nickname, c.avatar_url, d.name, d.phone')
                    ->order('a.id desc')
                    ->page($page, $limit)->select()->toArray();
                break;
            case 4:
                // 盲盒订单
                $query = Db::name('payment_platform_box')
                    ->alias('a')
                    ->join('platform_box_prize b', 'a.platform_box_prize_id = b.id')
                    ->join('user c', 'a.user_id = c.id')
                    ->join('order d', 'a.order_id = d.id', 'left')
                    ->where([['a.activity_id', '=', $activityId]]);
                
                // 用户信息筛选
                if (!empty($nickname)) {
                    $query->where('c.nickname', 'like', '%' . $nickname . '%');
                }
                if (!empty($realname)) {
                    $query->where('d.name', 'like', '%' . $realname . '%');
                }
                if (!empty($mobile)) {
                    $query->where('d.phone', 'like', '%' . $mobile . '%');
                }
                
                // 时间筛选
                if (!empty($timeWhere)) {
                    $query->where($timeWhere);
                }
                
                $data = $query->field('a.*, c.nickname, c.avatar_url, d.name, d.phone')
                    ->order('a.id desc')
                    ->page($page, $limit)->select()->toArray();
                break;
        }
        return $this->reSuccess('数据获取成功', $data ?? []);
    }

    /**
     * 店员列表
     * @param Request $request
     * @return Response
     */
    public function clerkList(Request $request) : Response
    {
        if (!request()->isGet()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->get('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $page = $request->get('page');
        $limit = $request->get('limit');

        $data = Db::name('user')
            ->alias('a')
            ->join('activity_user_role b', 'a.id = b.user_id')
            ->where(['b.activity_id' => $activityId, 'b.role' => 2])
            ->order('a.id desc')
            ->field('a.*')
            ->page($page, $limit)->select()->toArray();
        return $this->reSuccess('数据获取成功', $data);
    }

    /**
     * 删除店员
     * @param Request $request
     * @return Response
     */
    public function deleteClerk(Request $request) : Response
    {
        if (!request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->post('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $data = Db::name('activity_user_role')
            ->where(['activity_id' => $activityId, 'user_id' => $request->post('userId')])
            ->update(['staff_user_id' => 0]);
        return $this->reSuccess('删除成功', $data);
    }

    /**
     * 店员溯源订单
     * @param Request $request
     * @return Response
     */
    public function clerkSourceOrder(Request $request) : Response
    {
        if (!request()->isGet()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->get('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $clerkId = $request->get('clerkId');
        $status = $request->get('status');
        $page = $request->get('page');
        $limit = $request->get('limit');
        if ($status == 1) {
            $where = [['a.order_status', 'IN', [0 , 1]]];
        } else if ($status == 2) {
            $where = [['a.order_status', '=', 2]];
        }
        $data = Db::name('order')
            ->alias('a')
            ->join('user b', 'a.user_id = b.id')
            ->where($where ?? [])
            ->where(['a.activity_id' => $activityId, 'a.clerk_user_id' => $clerkId])
            ->field('a.*, b.nickname, b.avatar_url')
            ->order('a.id desc')->page($page, $limit)->select()->toArray();
        return $this->reSuccess('数据获取成功', $data);
    }

    /**
     * 核销记录
     * @return void
     */
    public function confirmPrizeList(Request $request) : Response
    {
        if (!request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->post('activityId');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $type = $request->post('type');
        $page = $request->post('page');
        $limit = $request->post('limit');

        switch ($type) {
            case 1:
                // 翻牌奖品
                $data = Db::name('payment_activity_prize')
                    ->alias('a')
                    ->join('activity_prize b', 'a.activity_prize_id = b.id')
                    ->join('user c', 'a.user_id = c.id')
                    ->join('order d', 'a.order_id = d.id')
                    ->join('user e', 'a.operator_user_id = e.id', 'left')
                    ->where([['a.activity_id', '=', $activityId], ['a.status', '=', 2]])
                    ->field('a.*, d.order_sn, c.nickname, c.avatar_url, d.name, d.phone, e.nickname as operator_nickname')
                    ->order('a.id desc')
                    ->page($page, $limit)->select()->toArray();
                break;
            case 2:
                // 盲盒奖品
                $data = Db::name('payment_platform_box')
                    ->alias('a')
                    ->join('platform_box_prize b', 'a.platform_box_prize_id = b.id')
                    ->join('user c', 'a.user_id = c.id')
                    ->join('order d', 'a.order_id = d.id')
                    ->join('user e', 'a.operator_user_id = e.id', 'left')
                    ->where([['a.activity_id', '=', $activityId], ['a.status', '=', 2]])
                    ->field('a.*, d.order_sn, c.nickname, c.avatar_url, d.name, d.phone, e.nickname as operator_nickname')
                    ->order('a.id desc')
                    ->page($page, $limit)->select()->toArray();
                break;
        }
        return $this->reSuccess('数据获取成功', $data);
    }

    /**
     * 意向客户
     * @param Request $request
     * @return Response
     */
    public function prospectCust(Request $request) : Response
    {
        if (request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->get('activityId');
        $page = $request->get('page');
        $limit = $request->get('limit');

        $data = Db::name('order')
            ->alias('o')
            ->join('user u', 'o.user_id = u.id')
            ->join('user ru', 'o.recom_user_id = ru.id')
            ->join('user cu', 'o.clerk_user_id = cu.id')
            ->whereOr([['o.order_status', '<>', 2], ['o.activity_id', '=', $activityId]])
            ->field('u.avatar_url, u.nickname as user_nickname, o.name, o.phone, o.customer_type, ru.nickname as recom_nickname, cu.nickname as clerk_nickname, o.create_time')
            ->order('o.id desc')
            ->page($page, $limit)->select()->toArray();

        return $this->reSuccess('数据获取成功', $data);
    }

    /**
     * 退款订单
     * @param Request $request
     * @return Response
     */
    public function refundOrder(Request $request) : Response
    {
        if (request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->get('activityId');
        $page = $request->get('page');
        $limit = $request->get('limit');

        $data = Db::name('order')
            ->alias('o')
            ->join('user u', 'o.user_id = u.id')
            ->join('user ru', 'o.recom_user_id = ru.id')
            ->join('user cu', 'o.clerk_user_id = cu.id')
            ->whereOr([['o.refund_status', '=', 1], ['o.activity_id', '=', $activityId]])
            ->field('u.avatar_url, u.nickname as user_nickname, o.name, o.phone, o.customer_type, ru.nickname as recom_nickname, cu.nickname as clerk_nickname, o.pay_time, o.create_time, o.refund_no')
            ->order('o.id desc')
            ->page($page, $limit)
            ->select()
            ->toArray();

        return $this->reSuccess('数据获取成功', $data);
    }

    /**
     * 红包记录
     * @param Request $request
     * @return Response
     */
    public function disburseList(Request $request) : Response
    {
        if (request()->isPost()) {
            return $this->reError('请求方式错误');
        }

        $activityId = $request->get('activity_id');
        if (empty($activityId) || $activityId == 'false') {
            return $this->reError('请重新进入活动');
        }

        $page = $request->get('page');
        $limit = $request->get('limit');

        $list = Db::name('payment_disburse')
            ->alias('d')
            ->join('user u', 'd.user_id = u.id')
            ->join('order o', 'd.order_id = o.id')
            ->where(['d.activity_id' => $activityId])
            ->field('d.*, u.nickname, u.avatar_url, o.name, o.phone, o.total_price, o.recom_user_id')
            ->order('d.id desc')->page($page, $limit)->select()->toArray();
        foreach ($list as &$value) {
            $value['source'] = Db::name('order')
                ->alias('o')
                ->join('user u', 'o.user_id = u.id', 'left')
                ->where(['o.id' => $value['order_id']])
                ->field('u.nickname, u.avatar_url, o.name, o.phone')
                ->find();
        }
        return $this->reSuccess('数据获取成功', $list);
    }

    /**
     * 手动录单
     * @param Request $request
     * @return Response
     */
    public function orderEntry(Request $request) : Response
    {
        if (request()->isGet()) {
            return $this->reError('请求方式错误');
        }

        $activity_id = $request->post('activity_id');
        $user_id = $request->post('user_id');
        $clerk_user_id = $request->post('clerk_user_id');
        $order_status = $request->post('order_status');
        $name = $request->post('name');
        $phone = $request->post('phone');
        $customer_type = $request->post('customer_type');

        // 判断用户是否存在
        if (empty(Db::name('user')->where(['id' => $user_id])->count())) {
            return $this->reError('[ID]'.$user_id.' 不存在');
        }
        if (empty(Db::name('user')->where(['id' => $clerk_user_id])->count())) {
            return $this->reError('[ID]'.$clerk_user_id.' 不存在');
        }

        // 判断是否为员工
        if (empty(Db::name('activity_user_role')->where(['activity_id' => $activity_id, 'user_id' => $clerk_user_id, 'role' => 2])->count())) {
            return $this->reError('该员工不存在');
        }

        // 查询用户预订单是否存在
        $order = Db::name('order')->where(['activity_id' => $activity_id, 'user_id' => $user_id])->find();
        if (empty($order)) {
            return $this->reError('请让用户访问活动页面');
        }

        try {
            Db::name('order')->where(['id' => $order['id']])->update([
                'order_type' => 2,
                'clerk_user_id' => $clerk_user_id,
                'order_status' => $order_status,
                'name' => $name,
                'phone' => $phone,
                'customer_type' => $customer_type,
            ]);
            return $this->reSuccess('手动录单成功');
        } catch (\Exception $e) {
            return $this->reError('手动录单失败');
        }
    }

    /**
     * 导出订单
     * @param Request $request
     * @return Response|bool
     */
    public function exportOrder(Request $request): Response|bool
    {
        try {
            // 解码获得活动 ID
            $activity_id = base64_decode(urldecode(input('code')));
            $where[] = ['a.activity_id', '=', $activity_id];

            // 获取所有员工数据
            $clerk_user_ids = Db::name('activity_user_role')->where(['activity_id' => $activity_id, 'role' => 2])->column('user_id');

            $list  = Db::name('activity_visitor')
                ->alias('a')
                ->join('order o', 'a.order_id = o.id', 'left')
                ->join('user u', 'a.user_id = u.id', 'left')
                ->join('user cu', 'o.clerk_user_id = cu.id', 'left')
                ->join('user ru', 'o.recom_user_id = ru.id', 'left')
                ->field('a.*')
                ->where($where)->order('a.id desc')->select()->toArray();
            foreach ($list as $key => $value) {
                $data = Db::name('order')->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id']])->find();
                $data['recom_nickname'] = Db::name('user')->where(['id' => $data['recom_user_id']])->value('nickname');
                $data['clerk_nickname'] = Db::name('user')->where(['id' => $data['clerk_user_id']])->value('nickname');
                // 销售数量
                $data['order_count'] = Db::name('order')->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id'], 'order_status' => 2])->count();
                // 销售金额
                $data['order_total_price'] = Db::name('order')->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id'], 'order_status' => 2])->sum('total_price');
                // 推荐奖励
                $data['payment_disburse_amount'] = Db::name('payment_disburse')
                    ->where(['status' => 1, 'activity_id' => $value['activity_id'], 'user_id' => $value['user_id']])
                    ->sum('amount');
                // 翻牌奖励
                $data['activity_prize_count'] = Db::name('payment_activity_prize')
                    ->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id']])
                    ->count();
                // 平台盲盒
                $data['platform_box_prize_count'] = Db::name('payment_platform_box')
                    ->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id']])
                    ->count();
                // 获取下单用户
                $user = Db::name('user')->where(['id' => $value['user_id']])->find();
                if (!empty($user)) {
                    $data['avatar_url'] = $user['avatar_url'];
                    $data['nickname'] = $user['nickname'];
                    $data['openid'] = $user['openid'];
                }
                // 是否为员工直推
                $data['is_clerk_recom'] = in_array($data['recom_user_id'], $clerk_user_ids) ? 1 : 0;

                $data['order_info'] = Db::name('order')->where(['activity_id' => $value['activity_id'], 'user_id' => $value['user_id']])->field('name, phone, customer_type')->find();
                $value = array_merge($value, $data);

                $excelData[$key] = [
                    $value['nickname'] ?? '', $value['openid'] ?? '', $value['order_info']['name'] ?? '', $value['order_info']['phone'] ?? '', $value['order_info']['customer_type'] ?? '', ($value['is_clerk_recom'] == 1 ? "员工直推" : "非直推"),
                    $value['order_sn'] ?? '', $value['pay_no'] ?? '', $value['total_price'] ?? '', ($value['order_status'] ?? '' == 2 ? "已支付" : "未支付"),
                    $value['payment_disburse_amount'] ?? '', $value['activity_prize_count'] ?? '', $value['platform_box_prize_count'] ?? '',
                    $value['recom_nickname'] ?? '', $value['clerk_nickname'] ?? '', $value['create_time'] ?? '',
                ];
            }
            $spreadsheet = excel::handleExcel([[
                'name' => '参与明细',
                'title' => [
                    '会员昵称', 'OPENID', '姓名', '手机号', '新客/老客', '员工直推/非直推',
                    '订单编号', '支付编号', '支付金额', '支付状态',
                    '推荐奖励', '翻牌奖励', '平台盲盒',
                    '上级', '溯源员工', '参与时间',
                ],
                'data' => $excelData ?? []
            ]], []);
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');

            $file_name = '参与明细';
            $file_path = runtime_path() . '/excel/' . $file_name . time() . '.' . mt_rand(100000, 999999) . '.xlsx';
            // 保存文件到 public 下
            @mkdir(runtime_path() . '/excel/');
            $writer->save($file_path);
            // 下载文件
            return response()->download($file_path, $file_name . '.xlsx');
        } catch (\Exception|\PhpOffice\PhpSpreadsheet\Exception$e) {
            return $this->reError($e->getMessage());
        }
    }

    /**
     * 获取导出订单地址
     * @param Request $request
     * @return Response|bool
     */
    public function getExcelUrl(Request $request): Response|bool
    {
        $activityId = $request->get('activity_id');
        return $this->reSuccess('成功', [
            'url' => getenv('API_HOST').'/api/staff/exportOrder?code='.urlencode(base64_encode($activityId))
        ]);
    }


    /**
     * 活动总览
     * @param Request $request
     * @return Response|bool
     */
    public function eventOverview(Request $request): Response|bool
    {
        $activity_id = $request->get('activity_id');
        $start_time = $request->get('start_time');
        $end_time = $request->get('end_time');

        if (!empty($start_time) && !empty($end_time)) {
            $where[] = ['create_time', 'between', [strtotime($start_time), strtotime($end_time)]];
        }

        $data = [
            // 数据统计
            'data_statistics' => [
                // 访问用户
                'visit_users' => Db::name('activity_operation_log')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['type', '=', 1]])->count(),
                // 邀请浏览
                'invite_views' => Db::name('activity_operation_log')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['type', '=', 2]])->count(),
                // 分享数量
                'share_count' => Db::name('activity_operation_log')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['type', '=', 3]])->count(),
                // 线上单数量
                'online_count' => Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['order_type', '=', 1]])->count(),
                // 线下单数量
                'offline_count' => Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['order_type', '=', 2]])->count(),
                // 员工数量
                'staff_count' => Db::name('activity_user_role')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['role', '=', 2]])->count(),
                // 支付用户
                'paid_users' => Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['order_status', '=', 2]])->count(),
            ],
            // 金额统计
            'financial_statistics' => [
                // 收款金额
                'received_amount' => $received_amount = Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['order_status', '=', 2]])->sum('total_price'),
                // 奖励金额
                'reward_amount' => $reward_amount = Db::name('payment_disburse')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['status', '=', 2]])->sum('amount'),
                // 退款金额
                'refund_amount' => $refund_amount = Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['refund_status', '=', 1]])->sum('total_price'),
                // 账户余额
                'account_balance' => $received_amount - $reward_amount - $refund_amount,
            ],
            // 新老客统计
            'customer_statistics' => [
                // 新客数
                'new_customer_count' => Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['customer_type', '=', '新客']])->count(),
                // 老客数
                'old_customer_count' => Db::name('order')->where($where ?? [])->where([['activity_id', '=', $activity_id], ['customer_type', '=', '老客']])->count(),
            ],
        ];

        return $this->reSuccess('成功', $data);
    }

    /**
     * 员工统计
     * @param Request $request
     * @return Response|bool
     * @throws \think\db\exception\BindParamException
     */
    public function staffStatistics(Request $request): Response|bool
    {
        $activity_id = (int)$request->get('activity_id');
        $start_time = $request->get('start_time');
        $end_time = $request->get('end_time');

        $whereSQL = "";
        if (!empty($start_time) && !empty($end_time))
        {
            $start_time = date('Y-m-d H:i:s', strtotime($start_time));
            $end_time = date('Y-m-d H:i:s', strtotime($end_time));
            $whereSQL = "
                AND create_time >= '{$start_time}'  -- 开始时间
                AND create_time <= '{$end_time}'  -- 结束时间
            ";
        }

        $data = Db::query("
            SELECT 
                t1.user_id,
                u.avatar_url,
                u.nickname,
                -- 直推维度
                COALESCE(t2.direct_order_amount, 0) as direct_order_amount,  -- 直推订单总额
                COALESCE(t2.direct_order_count, 0) as direct_order_count,    -- 直推订单总数
                COALESCE(t2.direct_user_count, 0) as direct_user_count,      -- 直推用户总数
                -- 溯源维度
                COALESCE(t3.trace_order_amount, 0) as trace_order_amount,    -- 溯源订单总额
                COALESCE(t3.trace_order_count, 0) as trace_order_count,      -- 溯源订单总数
                COALESCE(t3.trace_user_count, 0) as trace_user_count         -- 溯源用户总数
            FROM 
                tuoke_activity_user_role t1
                LEFT JOIN (
                    -- 直推统计
                    SELECT 
                        recom_user_id,
                        SUM(CASE WHEN order_status = 2 THEN total_price ELSE 0 END) as direct_order_amount,
                        COUNT(*) as direct_order_count,
                        COUNT(DISTINCT user_id) as direct_user_count
                    FROM 
                        tuoke_order
                    WHERE 
                        activity_id = {$activity_id}
                        AND recom_user_id IS NOT NULL
                        AND recom_user_id != 0
                        {$whereSQL}
                    GROUP BY 
                        recom_user_id
                ) t2 ON t1.user_id = t2.recom_user_id
                LEFT JOIN (
                    -- 溯源统计
                    SELECT 
                        clerk_user_id,
                        SUM(CASE WHEN order_status = 2 THEN total_price ELSE 0 END) as trace_order_amount,
                        COUNT(*) as trace_order_count,
                        COUNT(DISTINCT user_id) as trace_user_count
                    FROM 
                        tuoke_order
                    WHERE 
                        activity_id = {$activity_id}
                        AND clerk_user_id IS NOT NULL
                        {$whereSQL}
                    GROUP BY 
                        clerk_user_id
                ) t3 ON t1.user_id = t3.clerk_user_id
                LEFT JOIN tuoke_user u ON u.id = t1.user_id
            WHERE 
                t1.activity_id = {$activity_id}
                AND t1.role = 2
            ORDER BY 
                direct_order_amount DESC,
                direct_order_count DESC,
                direct_user_count DESC,
                trace_order_amount DESC,
                trace_order_count DESC,
                trace_user_count DESC;
        ");
        return $this->reSuccess('成功', $data);
    }

    /**
     * 链接访客记录
     * @param Request $request
     * @return Response|bool
     */
    public function visitorList(Request $request): Response|bool
    {
        try {
            $activityId = $request->get('activity_id', 0);
            $page = $request->get('page', 1);
            $limit = $request->get('limit', 10);

            if (empty($activityId)) {
                return $this->reError('活动ID不能为空');
            }

            $query = Db::name('activity_visitor')
                ->alias('av')
                ->leftJoin('user u', 'u.id = av.user_id')
                ->leftJoin('order o', 'o.user_id = av.user_id AND o.activity_id = av.activity_id')
                ->leftJoin('user clerk', 'clerk.id = o.clerk_user_id')
                ->leftJoin('activity_operation_log aol', 'aol.user_id = av.user_id AND aol.activity_id = av.activity_id')
                ->field([
                    'u.avatar_url',
                    'u.nickname',
                    'u.id as user_id',
                    'o.name',
                    'o.phone',
                    'o.order_status as pay_status',
                    'COUNT(DISTINCT aol.id) as visit_count',
                    'MAX(aol.create_time) as visit_time',
                    'clerk.nickname as clerk_nickname',
                    'o.create_time as join_time'
                ])
                ->where('av.activity_id', $activityId)
                ->group('av.user_id')
                ->order('o.create_time', 'desc');

            $list = $query->page($page, $limit)->select();

            // 处理数据
            foreach ($list as &$item) {
                if (empty($item['clerk_nickname'])) {
                    $item['clerk_nickname'] = '';
                }
            }

            return $this->reSuccess('成功', $list);
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }

    /**
     * 链接访客详情
     * @param Request $request
     * @return Response|bool
     */
    public function visitorDetail(Request $request): Response|bool
    {
        try {
            $activityId = $request->get('activity_id', 27);
            $userId = $request->get('user_id', 11);

            if (empty($activityId) || empty($userId)) {
                return $this->reError('参数不完整');
            }

            $query = Db::name('activity_visitor')
                ->alias('av')
                ->leftJoin('user u', 'u.id = av.user_id')
                ->leftJoin('order o', 'o.user_id = av.user_id AND o.activity_id = av.activity_id')
                ->leftJoin('user clerk', 'clerk.id = o.clerk_user_id')
                ->leftJoin('activity_operation_log aol', 'aol.user_id = av.user_id AND aol.activity_id = av.activity_id')
                ->field([
                    'u.avatar_url',
                    'u.nickname',
                    'o.name',
                    'o.phone',
                    'o.order_status as pay_status',
                    'COUNT(DISTINCT aol.id) as visit_count',
                    'MAX(aol.create_time) as visit_time',
                    'clerk.nickname as clerk_nickname',
                    'o.create_time as join_time'
                ])
                ->where('av.activity_id', $activityId)
                ->where('av.user_id', $userId)
                ->group('av.user_id');

            $data = $query->find();

            if ($data) {
                // 处理数据
                if (empty($data['clerk_nickname'])) {
                    $data['clerk_nickname'] = '';
                }
            }

            return $this->reSuccess('成功', $data);
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }

    /**
     * 获取用户最近操作记录
     * @param Request $request
     * @return Response
     */
    public function visitorOperationLog(Request $request)
    {
        try {
            // 参数验证
            $userId = (int)$request->get('user_id', 0);
            $activityId = (int)$request->get('activity_id', 0);
            $page = (int)$request->get('page', 1);
            $limit = (int)$request->get('page_size', 10);

            // 参数校验
            if ($userId <= 0) {
                return $this->reError('用户ID不能为空');
            }
            if ($activityId <= 0) {
                return $this->reError('活动ID不能为空');
            }

            // 验证用户是否存在
            $userExists = Db::name('user')->where('id', $userId)->count();
            if (!$userExists) {
                return $this->reError('用户不存在');
            }

            // 验证活动是否存在
            $activityExists = Db::name('activity')->where('id', $activityId)->count();
            if (!$activityExists) {
                return $this->reError('活动不存在');
            }

            // 构建查询
            $query = Db::name('activity_operation_log')
                ->alias('ol')
                ->leftJoin('user u', 'u.id = ol.user_id')
                ->leftJoin('order o', 'o.user_id = ol.user_id AND o.activity_id = ol.activity_id')
                ->leftJoin('user clerk', 'clerk.id = o.clerk_user_id')
                ->field([
                    'ol.id',
                    'ol.create_time',
                    'ol.type as operation_type',
                    'ol.record as operation_desc',
                    'u.nickname as operator_name',
                    'u.avatar_url as operator_avatar',
                    'clerk.nickname as clerk_nickname'
                ])
                ->where('ol.user_id', $userId)
                ->where('ol.activity_id', $activityId)
                ->order('ol.create_time', 'desc');

            // 获取分页数据
            $list = $query->page($page, $limit)->select();

            // 处理操作类型显示
            $operationTypes = [
                1 => '访问用户',
                2 => '邀请浏览',
                3 => '分享活动'
            ];

            // 处理数据
            foreach ($list as &$item) {
                $item['operation_type_text'] = $operationTypes[$item['operation_type']] ?? '未知类型';
                // 格式化时间
                $item['create_time'] = date('Y-m-d H:i:s', strtotime($item['create_time']));
                // 处理头像URL
                if (empty($item['operator_avatar'])) {
                    $item['operator_avatar'] = '/upload/portrait.png';
                }
                // 处理溯源员工昵称
                if (empty($item['clerk_nickname'])) {
                    $item['clerk_nickname'] = '';
                }
            }

            return $this->reSuccess('成功', $list);
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }

    /**
     * 获取用户跟进记录列表
     * @param Request $request
     * @return Response
     */
    public function customerFollowUpList(Request $request)
    {
        try {
            $userId = $request->get('user_id', 0);
            $activityId = $request->get('activity_id', 0);
            $page = $request->get('page', 1);
            $limit = $request->get('limit', 10);

            if (empty($activityId) && empty($activityId)) {
                return $this->reError('用户ID和活动ID不能为空');
            }

            $query = Db::name('follow_up')
                ->alias('fu')
                ->leftJoin('user u', 'u.id = fu.clerk_user_id')
                ->field([
                    'fu.id',
                    'fu.follow_type',
                    'fu.remark',
                    'fu.next_visit_time',
                    'fu.create_time',
                    'u.nickname as operator_name',
                    'u.avatar_url as operator_avatar'
                ])
                ->where('fu.user_id', $userId)
                ->where('fu.activity_id', $activityId)
                ->order('fu.create_time', 'desc');

            $list = $query->page($page, $limit)->select();

            // 处理跟进类型显示
            $followTypes = [
                1 => '已经成交',
                2 => '没有意向',
                3 => '正在考虑',
                4 => '其他'
            ];

            foreach ($list as &$item) {
                $item['follow_type_text'] = $followTypes[$item['follow_type']] ?? '未知类型';
            }

            return $this->reSuccess('成功', $list);
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }

    /**
     * 提交跟进记录
     * @param Request $request
     * @return Response
     */
    public function customerSubmitFollowUp(Request $request)
    {
        try {
            $userId = $request->post('user_id', 0);
            $activityId = $request->post('activity_id', 0);
            $followType = $request->post('follow_type', 0);
            $remark = $request->post('remark', '');
            $nextVisitTime = $request->post('next_visit_time', '');

            if (empty($userId) || empty($activityId) || empty($followType)) {
                return $this->reError('参数不完整');
            }

            // 验证跟进类型
            $validTypes = [1, 2, 3, 4];
            if (!in_array($followType, $validTypes)) {
                return $this->reError('无效的跟进类型');
            }

            // 验证下次回访时间格式
            if ($nextVisitTime) {
                if (!strtotime($nextVisitTime)) {
                    return $this->reError('无效的回访时间格式');
                }
            }

            $data = [
                'activity_id' => $activityId,
                'user_id' => $userId,
                'clerk_user_id' => JwtToken::getExtend()['id'], // 假设从请求中获取当前用户ID
                'follow_type' => $followType,
                'remark' => $remark,
                'next_visit_time' => $nextVisitTime ?: null,
                'create_time' => date('Y-m-d H:i:s')
            ];

            $result = Db::name('follow_up')->insert($data);

            if ($result) {
                return $this->reSuccess('提交成功');
            } else {
                return $this->reError('提交失败');
            }
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }

    /**
     * 获取推荐排行
     * @param Request $request
     * @return Response
     */
    public function ranking(Request $request)
    {
        try {
            $activityId = $request->get('activity_id', 0);
            $type = $request->get('type', 1); // 1:员工排行 2:非员工排行
            $page = $request->get('page', 1);
            $limit = $request->get('limit', 10);

            if (empty($activityId)) {
                return $this->reError('活动ID不能为空');
            }

            // 基础查询
            $query = Db::name('activity_user_role')
                ->alias('aur')
                ->leftJoin('user u', 'u.id = aur.user_id')
                ->leftJoin('order o', 'o.recom_user_id = aur.user_id AND o.activity_id = aur.activity_id AND o.order_status = 2')
                ->leftJoin('payment_disburse pd', 'pd.recom_user_id = aur.user_id AND pd.activity_id = aur.activity_id AND pd.status = 1')
                ->leftJoin('activity_operation_log aol_view', 'aol_view.user_id = o.user_id AND aol_view.activity_id = aur.activity_id AND aol_view.type = 2')
                ->leftJoin('activity_operation_log aol_share', 'aol_share.user_id = o.user_id AND aol_share.activity_id = aur.activity_id AND aol_share.type = 3')
                ->field([
                    'aur.user_id',
                    'u.avatar_url',
                    'u.nickname',
                    'COUNT(DISTINCT aol_view.id) as visit_count', // 被推荐用户的浏览量
                    'COUNT(DISTINCT aol_share.id) as share_count', // 被推荐用户的分享量
                    'COUNT(DISTINCT o.id) as recommend_count', // 直推数（成功支付的订单数）
                    'COALESCE(SUM(pd.amount), 0) as reward_amount' // 奖励红包总额
                ])
                ->where('aur.activity_id', $activityId)
                ->group('aur.user_id');

            // 根据类型筛选
            if ($type == 1) {
                $query->where('aur.role', 2); // 员工
            } else {
                $query->where('aur.role', 0); // 非员工
            }

            // 获取列表数据
            $list = $query->page($page, $limit)
                ->order('reward_amount DESC, recommend_count DESC, share_count DESC, visit_count DESC')
                ->select()
                ->toArray();

            // 获取所有用户ID
            $userIds = array_column($list, 'user_id');

            // 获取直推订单金额
            $directOrders = Db::name('order')
                ->where('activity_id', $activityId)
                ->where('order_status', 2)
                ->whereIn('recom_user_id', $userIds)
                ->field('recom_user_id, SUM(total_price) as direct_amount')
                ->group('recom_user_id')
                ->select()
                ->toArray();

            // 获取溯源订单金额
            $traceOrders = Db::name('order')
                ->where('activity_id', $activityId)
                ->where('order_status', 2)
                ->whereIn('clerk_user_id', $userIds)
                ->field('clerk_user_id, SUM(total_price) as trace_amount')
                ->group('clerk_user_id')
                ->select()
                ->toArray();

            // 转换为以用户ID为键的数组
            $directAmountMap = array_column($directOrders, 'direct_amount', 'recom_user_id');
            $traceAmountMap = array_column($traceOrders, 'trace_amount', 'clerk_user_id');

            // 添加排名和金额数据
            $rank = ($page - 1) * $limit + 1;
            foreach ($list as &$item) {
                $item['rank'] = $rank++;
                // 格式化金额为2位小数
                $item['reward_amount'] = number_format($item['reward_amount'], 2, '.', '');
                $item['direct_amount'] = number_format($directAmountMap[$item['user_id']] ?? 0, 2, '.', '');
                $item['trace_amount'] = number_format($traceAmountMap[$item['user_id']] ?? 0, 2, '.', '');
            }

            return $this->reSuccess('成功', $list);
        } catch (\Exception $e) {
            return $this->reError('系统错误：' . $e->getMessage());
        }
    }
}
